import logging

from fastapi import APIRouter, Depends, Query
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

danger_level_router = APIRouter(prefix="/danger/dangerLevel", tags=["风险等级管理"],
                                dependencies=[Depends(oauth2_scheme)])


class DangerLevelSearch(PaginationBase):
    flevelname: str = Field("", description="风险等级名")


@danger_level_router.get("/list", summary="风险等级查询接口")
def list_danger_level(level: DangerLevelSearch = Query(), db: Session = Depends(get_db)):
    param = {"flevelname": level.flevelname}
    sql = "select count(1) from s_ts_danger_level where 1=1"
    if level.flevelname != "":
        sql = sql + " and flevelname like concat('%', :flevelname ,'%')"
    sql = sql + " order by  flevelno "
    count = db.execute(text(sql), param).scalar()

    sql = "select flevelno,flevelscre,flevelname,fupddate,foperator"
    sql = sql + " from s_ts_danger_level where 1=1"
    if level.flevelname != "":
        sql = sql + " and flevelname like concat('%', :flevelname ,'%')"
    sql = sql + " order by  flevelno "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = level.limit
    param["offset"] = level.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['flevelno'] = data[key][0]
        d1['flevelscre'] = data[key][1]
        d1['flevelname'] = data[key][2]
        d1['fupddate'] = data[key][3]
        d1['foperator'] = data[key][4]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": level.limit,
                 "page": level.pageIndex,
                 "pages": (count + level.limit - 1) // level.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class DangerLevelCreate(BaseModel):
    """风险等级新增请求"""
    flevelname: str = Field(..., description="等级名称")
    flevelno: str = Field(..., description="等级编号")
    flevelscre: str = Field(..., description="等级分数")
    foperator: int = Field(..., description="操作人ID")
    fupddate: str = Field(..., description="更新日期(yyyy-MM-ddTHH:mm:ss)")


@danger_level_router.post("/create", summary="风险等级新增接口")
def create_danger_level(danger: DangerLevelCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select flevelno from s_ts_danger_level where flevelno=:flevelno "
            data = db.execute(text(sql), {"flevelno": danger.flevelno}).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="编号已存在")
            else:
                sql = "insert into s_ts_danger_level(flevelno,flevelscre,flevelname,fupddate,foperator)"
                sql = sql + " values( :flevelno , :flevelscre , :flevelname ,now(), :foperator );"
                db.execute(text(sql), {
                    "flevelno": danger.flevelno,
                    "flevelscre": danger.flevelscre,
                    "flevelname": danger.flevelname,
                    "foperator": danger.foperator,
                })
                # 返回
                d1 = {}
                d1['flevelname'] = danger.flevelname
                d1['flevelno'] = danger.flevelno
                d1['flevelscre'] = danger.flevelscre
                d1['foperator'] = danger.foperator
                d1['fupddate'] = danger.fupddate
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@danger_level_router.post("/update", summary="修改风险等级")
def update_danger_level(danger: DangerLevelCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select flevelno from s_ts_danger_level where flevelname= :flevelname and flevelno<> :flevelno "
            data = db.execute(text(sql), {
                "flevelname": danger.flevelname,
                "flevelno": danger.flevelno
            }).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="风险等级名称已存在")
            else:
                sql = "update s_ts_danger_level"
                sql = sql + " set flevelname= :flevelname ,flevelscre= :flevelscre "
                sql = sql + " ,fupddate=now(),foperator= :foperator "
                sql = sql + " where flevelno= :flevelno "
                # print(sql)
                db.execute(text(sql), {
                    "flevelname": danger.flevelname,
                    "flevelscre": danger.flevelscre,
                    "foperator": danger.foperator,
                    "flevelno": danger.flevelno,
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@danger_level_router.post("/delete/{flevelno}", summary="删除风险等级")
def delete_danger_level(flevelno: int, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select count(1) from a_ts_airwarring where flevelno= :flevelno "
            data = db.execute(text(sql), {
                "flevelno": flevelno
            }).fetchall()

            if data[0][0] > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from s_ts_danger_level where flevelno= :flevelno "
                db.execute(text(sql), {
                    "flevelno": flevelno
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
